﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Windows.Forms;
namespace skald_db
{
    public class db_to_app_layer
    {
 
        public enum commands { select, delete, update, insert, deploy, drop, deploy_all };
        public const string default_server_string = @"Data Source=NADEJDA\SQLEXPRESS;Integrated Security=True";
        SqlConnection m_conn = null;
 
        bool state;
        /// <summary>
        /// need to connection create
        /// </summary>
        public db_to_app_layer(){     
            state = false;
            
        }
        /// <summary>
        /// tries to connect only to srever
        /// </summary>
        /// <param name="server_only_string">without initial catalog</param>
        /// <returns></returns>
        public bool connect(string server_only_string,string kal = "")
        {
            m_conn = new SqlConnection(server_only_string);
            try
            {
                m_conn.Open();
                state = true;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
                return false;
            }
            return true;
        }
        /// <summary>
        /// tries to connect directly to db
        /// </summary>
        /// <param name="full_connection_string">server & db name full</param>
        /// <returns></returns>
        public bool connect(string full_connection_string )
        {
            m_conn = new SqlConnection(full_connection_string);
            try
            {
                m_conn.Open();
                state = true;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
                return false;
            }
            return true;
        }

        public List<Array> main_data_buffer{get;set;}

        /// <summary>
        ///       
        /// true if deployed
        /// deploy
        ///     tables
        ///     procs
        ///     funcs
        ///     roles
        ///     inits whs
        /// false if fail
        /// </summary>
        /// <param name="db_name">name</param>
        /// <param name="whs_length">stallaji</param>
        /// <param name="whs_width">polki</param>
        /// <param name="deploy_file">relative path C:\Users\Anton\Desktop\STUDY\БД - курс\sklad\sql_scripts\sql_deploy_scripts\</param>
        /// <param name="create_db">if true creats db if not creates all IN dbname</param>
        /// <returns>if fail false</returns>
        public bool deploy(string db_name, int whs_length = 100, int whs_width = 100, string script_folder = @" C:\Users\Anton\Desktop\STUDY\БД - курс\sklad\sql_scripts\sql_deploy_scripts\", bool create_db = true)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = m_conn;
            string txt = System.IO.File.ReadAllText(script_folder+"deploy_tables.sql");
            if (create_db) {
                txt = "create database " + db_name +"\n" + txt;
            }
            cmd.CommandText = txt;
            cmd.Connection = m_conn;
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch(Exception e)
            {
                MessageBox.Show(e.Message);
                return false;
            }
            // -------  procs
            txt = System.IO.File.ReadAllText(script_folder + "deploy_funcs_procs.sql");
            cmd.CommandText = txt;
            try
            {
                cmd.Parameters.AddWithValue("@length", whs_length);
                cmd.Parameters.AddWithValue("@width", whs_width);
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
                return false;
            }
            //---------roles

            // init execs
            txt = System.IO.File.ReadAllText(script_folder + "execute_on_deploy.sql");
            cmd.CommandText = txt;
            try
            {
                cmd.Parameters.AddWithValue("@length", whs_length);
                cmd.Parameters.AddWithValue("@width", whs_width);
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
                return false;
            }
            return true;
        }
        /// <summary>
        /// check   this.main_data_buffer.rows  after true to recive list of arrays
        /// </summary>
        /// <param name="table"></param>
        /// <param name="fields"></param>
        /// <returns></returns>
        public bool select(string table,string fields="*") {

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = m_conn;
            cmd.CommandText = string.Format("Select {1} from {0}", table, fields);
            try
            {
                SqlDataReader rdr = cmd.ExecuteReader();
                this.main_data_buffer = new List<Array>();
                while (rdr.Read())
                {
                    object[] get = new object[rdr.FieldCount];
                    rdr.GetValues(get);
                    this.main_data_buffer.Add(get);
                }
                rdr.Close();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
                return false;
            }
            return true; 
        }
        public bool drop_db(string db_to_drop_name){
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = m_conn;
            cmd.CommandText = "drop database "+ db_to_drop_name;
            cmd.Connection = m_conn;
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch(Exception e)
            {
                MessageBox.Show(e.Message);
                return false;
            }
            return true;
        }
        public bool get_class_nodes(int parent_id = 0) {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = m_conn;
            cmd.CommandText = "exec get_class_childs "+ parent_id.ToString();
            try
            {
                SqlDataReader rdr = cmd.ExecuteReader();
                this.main_data_buffer = new List<Array>();
                while (rdr.Read())
                {
                    object[] get = new object[rdr.FieldCount];
                    rdr.GetValues(get);
                    this.main_data_buffer.Add(get);
                }
                rdr.Close();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
                return false;
            }
            return true;
        
        }
        public bool execute_proc(string paramts) {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = m_conn;
            cmd.CommandText = paramts;
            try
            {
                SqlDataReader rdr = cmd.ExecuteReader();
                this.main_data_buffer = new List<Array>();
                while (rdr.Read())
                {
                    object[] get = new object[rdr.FieldCount];
                    rdr.GetValues(get);
                    this.main_data_buffer.Add(get);
                }
                rdr.Close();
            }
            catch(Exception e)
            {
                MessageBox.Show( e.Message);
                return false;
            }
            return true;
        }

        public bool delete_table(string tb_name) {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = m_conn;
            cmd.CommandText ="use "+m_conn.Database+ "\ndelete from "+tb_name;
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
                return false;
            }
            return true;
 
        }
        public bool is_connected {
            get{
                return state;
            }
        }
        public string db_name {
            get {
                if (m_conn == null || m_conn.Database == "") return "";
                return m_conn.Database;
            }
        
        }
        public bool search_class_name(string class_name,bool get_all) {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = m_conn;
            cmd.CommandText ="use" + db_name +"select [class_name] from [dbo].[class] where [class_name]=" + class_name;
            try
            {
                    if(cmd.ExecuteNonQuery()>0){
                            MessageBox.Show("Существует");
                        return true;
                    }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
                return false;
            }
            return true;
        
        
        }
        public bool insert_class_node(string name, string fav,bool is_it) {
            int favb = Convert.ToInt32(fav);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = m_conn;
            int flag = is_it? 1 : 0;
            cmd.CommandText = "use " + db_name + "\n INSERT INTO [class]  ([parent_node_id],[class_name] ,[is_item]) VALUES("+fav+",'"+name+"',"+flag+" )";
            try {
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
                return false;
            }

            return true;
        
        }
        public bool change_db(string db_name_to_change) {

            try
            {
                m_conn.ChangeDatabase(db_name_to_change);
            }
            catch (Exception e) {
                MessageBox.Show(e.Message);
                return false;
            }
            Program.global_db_name = db_name_to_change;
            return true;
        }
        public bool get_all_records() {

            string txt = @"     use " + m_conn.Database + @"
                            SELECT [record_id] 
                                ,[record_date]
                                ,[record_goods_id]
                                ,[class_name]
                                ,[record_amount]
                                ,[prefix]
                                ,[place] / dbo.get_whs_length () + 1
                                ,[place] % dbo.get_whs_width ()

                                ,[record_income_flag]   
,[record_name]
                                ,[record_desc]
                            FROM  [history] , [class] , [goods]
                            where class.node_id = goods.name  and goods.id = history.[record_goods_id]  ";

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = m_conn;
            cmd.CommandText = txt;
            try
            {
                SqlDataReader rdr = cmd.ExecuteReader();
                this.main_data_buffer = new List<Array>();
                while (rdr.Read())
                {
                    object[] get = new object[rdr.FieldCount];
                    rdr.GetValues(get);
                    this.main_data_buffer.Add(get);
                }
                rdr.Close();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
                return false;
            }
            return true;
        
        }

    }
}



 



